package dao;

import beans.DocCabeceraTo;
import beans.ProductoTo;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.Iterator;

import utildb.GenericDAO;
import utildb.Transaction;
import utildb.TransactionException;


public class RegistrarVentasDAO extends GenericDAO {

    public RegistrarVentasDAO(Transaction transacction) {
        super(transacction);
    }

    public int getNumeDocu(String pNumeSerie ) throws TransactionException {
                String lc_SQL;
                int nNumero = 0;
                Connection c;
		Statement statement = null;
		ArrayList all = null;
		try {
			c = transaction.getConnection();
			statement = c.createStatement();
                        lc_SQL = "Select max(nNum_Docu) nNum_Docu " +
                                  " From mysql.vetc_Docu " +
                                 " Where cNum_Seri = '" + pNumeSerie + "'";
			ResultSet rs = statement.executeQuery(lc_SQL);
			all = new ArrayList();
			while (rs.next()) {
                              nNumero = rs.getInt("nNum_Docu");
			}
			rs.close();
			statement.close();
		} catch (SQLException e1) {
			throw new TransactionException(e1);
		}
		nNumero++;
                return nNumero;

    }

    public void grabarNewVenta(DocCabeceraTo oCabe,
                               ArrayList oDeta ) throws TransactionException {
           
           String cNumSerie = "001001";
           String lc_Sql;
           try {
                    int i = 1;
                    Connection c;
                    PreparedStatement statement = null;
                    c = (Connection) transaction.getConnection();
                    int numeDocu = getNumeDocu(cNumSerie);
                    lc_Sql = "insert into mySql.Vetc_Docu " +
                                 " (cLug_Entr_Merc, cEst_Docu, nTot_Vent, " +
                                  " cPer_Cont_Merc, dFec_Entr_Merc, cHor_Entr_Merc, " +
                                  " cTel_Pers_Cont, nNum_Docu, cCod_Usua, " +
                                  " cRuc_Clie, cNum_Seri, nImp_IGV, " +
                                  " nSub_Tota_Vent, dFec_Vent, cTip_Mone_Vent ) " +
                         " values (?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,? )";
                                // 1 2 3  4 5 6  7 8 9 101112 131415
                    //
                    statement = c.prepareStatement(lc_Sql);

                    statement.setString(i++, oCabe.getcLug_Entr_Merc());
                    statement.setString(i++, oCabe.getcEst_Docu());
                    statement.setDouble(i++, oCabe.getnTot_Vent());
                    //
                    statement.setString(i++, oCabe.getcPer_Cont_Merc());
                    statement.setDate(i++, oCabe.getdFec_Entr_Merc());
                    statement.setString(i++, oCabe.getcHor_Entr_Merc());
                    //
                    statement.setString(i++, oCabe.getcTel_Pers_Cont());
                    statement.setInt(i++, numeDocu );
                    statement.setString(i++, oCabe.getcCod_Usua());
                    //
                    statement.setString(i++, oCabe.getcRuc_Clie());
                    statement.setString(i++, cNumSerie );
                    statement.setDouble(i++, oCabe.getnImp_IGV());
                    //
                    statement.setDouble(i++, oCabe.getnSub_Tota_Vent());
                    statement.setDate(i++, (Date) oCabe.getdFec_Vent());
                    statement.setString(i++, oCabe.getcTip_Mone_Vent());
                    //
                    int n = statement.executeUpdate();
                    statement.close();
                    if (n == 0)
                            throw new TransactionException("No se pudo crear el producto");

                    // AHORA GRABAR EL DETALLE
                    for (Iterator it = oDeta.iterator(); it.hasNext();) {
                         ProductoTo oProd = (ProductoTo) it.next();
                         statement = c.prepareStatement("insert into mySql.Vetd_Docu " +
                                      " (cNum_Seri, nNum_Docu, cCod_Prod, " +
                                       " cRuc_Prov, ncan_Vent, " +
                                       " cTip_Mone_Vent, nPre_Vent ) " +
                              " values (?,?,?, ?,?, ?,?)");
                                     // 1 2 3  4 5  6 7
                         i = 1;
                         String cCodProd = oProd.getCcod_Prod();
                         String cRucProv = oProd.getCruc_Prov();
                         double ncan_Vent = oProd.getNcan_Vent();
                         String cMonVent = oProd.getCtip_Mone_Vent();
                         Double nPreVent = oProd.getNpre_Vent();
                         //
                         statement.setString(i++, cNumSerie );
                         statement.setInt(i++, numeDocu);
                         statement.setString(i++, cCodProd);
                         //
                         statement.setString(i++, cRucProv);
                         statement.setDouble(i++, ncan_Vent);
                         statement.setString(i++, cMonVent);
                         statement.setDouble(i++, nPreVent);
                         //
                         int nD = statement.executeUpdate();
                         statement.close();
                         if (nD == 0)
                                throw new TransactionException("No se pudo crear el producto");
                    }
                    //

            } catch (SQLException e1) {
                    throw new TransactionException(e1);
            }
    }

    public void eliminar(String codProd ) throws TransactionException {
           throw new TransactionException("No soportado");
    }

    public DocCabeceraTo getDocCabecera(String cNumeSeri,
                                     int nNumeDocu )
                                     throws TransactionException {
            Connection c;
            Statement statement = null;
            String ccadSql = "";
            DocCabeceraTo oCabe = new DocCabeceraTo();
            //
            try {
                    c = transaction.getConnection();
                    statement = c.createStatement();
                    ccadSql = "select b.cnom_usua, " +
                                    " a.* " +
                              " From mySql.VETC_Docu a, mySql.GEMC_Usua b " +
                             " Where a.cRuc_Prov =  b.cRuc_Usua " +
                               " And a.cNum_Seri = '" + cNumeSeri + "' " +
                               " And a.nNum_Docu = " + nNumeDocu + " " +
                            "  Order By 1";

                    ResultSet rs = statement.executeQuery(ccadSql);
                    while (rs.next()) {
//                        DocCabeceraTo oCabe;
                        oCabe.setcNum_Seri( cNumeSeri );
                        oCabe.setnNum_Docu( nNumeDocu );
                        oCabe.setcRuc_Clie( rs.getString("cRuc_Clie"));
                        oCabe.setcNom_Clie( rs.getString("cnom_usua"));
                    }
                    rs.close();
                    statement.close();
            } catch (SQLException e1) {
                    throw new TransactionException(e1);
            }
            return oCabe;
    }


    public ArrayList getBuscarDocumentos(String cRucProd )
                                         throws TransactionException {
            Connection c;
            Statement statement = null;
            String ccadSql = "";
            ArrayList listaDocumentos = new ArrayList();
            try {
                    c = transaction.getConnection();
                    statement = c.createStatement();
                    ccadSql = "select b.cnom_usua, " +
                                    " a.* " +
                              " From mySql.VETC_Docu a, mySql.GEMC_Usua b " +
                             " Where a.cRuc_Prov =  b.cRuc_Usua " +
                               " And a.cRuc_Prov Like '" + cRucProd + "' " +
                            "  Order By 1";

                    ResultSet rs = statement.executeQuery(ccadSql);
                    while (rs.next()) {
                        DocCabeceraTo oCabe = new DocCabeceraTo();
                        oCabe.setcNum_Seri( rs.getString("cNum_Seri") );
                        oCabe.setnNum_Docu(rs.getInt("nNum_Docu") );
                        oCabe.setcRuc_Clie( rs.getString("cRuc_Clie"));
                        oCabe.setcNom_Clie( rs.getString("cnom_usua"));
                        listaDocumentos.add( oCabe );
                    }
                    rs.close();
                    statement.close();
            } catch (SQLException e1) {
                    throw new TransactionException(e1);
            }
            return listaDocumentos;
    }


}
